﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;
using System.Drawing;
using System.Configuration;
using System.CodeDom;
using System.Xml.Linq;
using System.IO;
using Microsoft.SqlServer.Server;
using System.Xml.XPath;

namespace Web_Project
{
    public partial class test_6_1 : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                dataselect("Select CategoryId,Name from Category", D1);
                dataselect("Select ProductId,Name from Product", D2);
                dataselect("Select CategoryId,Name from Category", D3);
                dataselect("Select SuppId,Name from Supplier", D4);
            }
        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            // 获取用户输入的分类名称
            string categoryName = TextBox1.Text.Trim();
            // 定义数据库连接字符串
            string connectionString = "Data Source=XUNDONGHUAN;Initial Catalog=mfd_sever;Integrated Security=True";
            string sql;
            SqlCommand cmd;
            // 使用using语句创建数据库连接，确保连接会被正确关闭和释放
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                if (string.IsNullOrEmpty(categoryName))
                {
                    // 如果输入为空，查询所有卡通分类表的内容
                    sql = @"SELECT * FROM ctCategory";
                    cmd = new SqlCommand(sql, conn);
                }
                else
                {
                    // 如果有输入，按分类名称包含匹配查询（任意位置包含输入文本）
                    sql = @"SELECT c.ct_name, c.ct_time, c.ct_au, c.ct_dq, c.ct_com, c.ct_CategoryId, cat.Name AS CategoryName
                    FROM Cartoon c 
                    INNER JOIN ctCategory cat ON c.ct_CategoryId = cat.ct_CategoryId 
                    WHERE cat.Name LIKE '%' + @CategoryName + '%'";
                    // 创建SQL命令对象并添加参数
                    cmd = new SqlCommand(sql, conn);
                    cmd.Parameters.AddWithValue("@CategoryName", categoryName);
                }
                // 创建数据适配器执行查询
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                // 创建DataTable存储查询结果
                DataTable dt = new DataTable();
                // 填充DataTable
                adapter.Fill(dt);
                // 将查询结果绑定到GridView控件
                GridView1.DataSource = dt;
                GridView1.DataBind();
            }
        }

        public void dataselect(string sql, DropDownList dd)
        {
            SqlConnection con = new SqlConnection("Data Source=XUNDONGHUAN;Initial Catalog=mfd_sever;Integrated Security=True");
            SqlCommand cmd = new SqlCommand(sql, con);
            con.Open();
            SqlDataReader rd = cmd.ExecuteReader();
            while (rd.Read())
            {
                ListItem Lt = new ListItem();
                Lt.Text = rd[1].ToString();
                Lt.Value = rd[0].ToString();
                dd.Items.Add(Lt);
            }
            con.Close();
        }

        public void ins_up_del_data(string sql)
        {
            SqlConnection con = new SqlConnection("Data Source=XUNDONGHUAN;Initial Catalog=mfd_sever;Integrated Security=True");
            SqlCommand cmd = new SqlCommand(sql, con);
            try
            {
                con.Open();
                int k = cmd.ExecuteNonQuery();
                if (k > 0)
                {
                    Label2.Text = "操作成功！";
                    D1.Items.Clear();
                    dataselect("Select CategoryId,Name from Category", D1);
                }
            }
            catch (Exception ex)
            {
                Label2.Text = "操作错误：" + ex.Message.ToString();
            }
            finally
            {
                con.Close();
            }
        }
        protected void tainjia_Click(object sender, EventArgs e)
        {
            //添加商品分类
            ins_up_del_data("Insert into Category(Name,Descn) values('" + fenlei_mc_Tbox.Text + "','" + Tbox_miaoshu.Text + "')");
        }

        protected void D1_SelectedIndexChanged(object sender, EventArgs e)
        {
            //选择分类，读取信息：名称，描述，显示到文本框
            SqlConnection con = new SqlConnection("Data Source=XUNDONGHUAN;Initial Catalog=mfd_sever;Integrated Security=True");
            SqlCommand cmd = new SqlCommand("Select Name,Descn from Category where CategoryId=" + D1.SelectedValue.ToString(), con);
            con.Open();
            //执行命名的结果给reader对象
            SqlDataReader rd = cmd.ExecuteReader();
            while (rd.Read())
            {
                fenlei_mc_Tbox.Text = rd["Name"].ToString();
                Tbox_miaoshu.Text = rd["Descn"].ToString();
            }
            con.Close();
        }
        protected void xiugai_Click(object sender, EventArgs e)
        {
            ins_up_del_data("Update Category set Name='" + fenlei_mc_Tbox.Text +"',Descn='" + Tbox_miaoshu.Text +"' where CategoryID=" + D1.SelectedValue.ToString()+"");
        }

        protected void shanchu_Click1(object sender, EventArgs e)
        {
            ins_up_del_data("Delete from Category where CategoryID=" + D1.SelectedValue.ToString());
        }

        protected void Button2_Click(object sender, EventArgs e)
        {
            //添加商品信息
            SqlConnection con = new SqlConnection("Data Source=XUNDONGHUAN;Initial Catalog=mfd_sever;Integrated Security=True");
            try
            {
                // 先处理图片上传
                string imagePath = ""; // 初始化图片路径

                if (FileUpload1.HasFile)  // 检查是否选择了文件
                {
                    string fileExtension = Path.GetExtension(FileUpload1.FileName).ToLower();

                    if (fileExtension == ".jpg" || fileExtension == ".gif")
                    {
                        // 定义物理目录和文件名
                        string physicalDir = Server.MapPath("images\\");
                        string fileName = FileUpload1.FileName;

                        // 确保目录存在
                        if (!Directory.Exists(physicalDir))
                        {
                            Directory.CreateDirectory(physicalDir);
                        }
                        // 保存文件
                        FileUpload1.SaveAs(physicalDir + fileName);
                        imagePath = "/images/" + fileName;  // 设置数据库存储的路径
                        Label4.Text = "图片上传成功";
                        //显示图片
                        Image1.ImageUrl = imagePath;
                    }
                    else
                    {
                        Label4.Text = "文件类型错误，请选择JPG或GIF文件。";
                        return; // 终止执行
                    }
                }
                else
                {
                    Label4.Text = "请选择商品图片！";
                    return; // 终止执行
                }
                con.Open();
                string sql = "Insert into Product(CategoryId,ListPrice,UnitCost,SuppId,Name,Descn,Image,Qty) values(@CategoryId,@ListPrice,@UnitCost,@SuppId,@Name,@Descn,@Image,@Qty)";
                SqlCommand cmd = new SqlCommand(sql, con);
                //在命令对象中定义每个参数的数据类型，并赋值
                cmd.Parameters.Add("@CategoryId",SqlDbType.Int).Value= D3.SelectedValue.ToString();
                cmd.Parameters.Add(@"ListPrice", SqlDbType.Decimal).Value = TextBox2.Text;
                cmd.Parameters.Add("@UnitCost", SqlDbType.Decimal).Value = TextBox3.Text;
                cmd.Parameters.Add("@SuppId",SqlDbType.Int).Value=D4.SelectedValue.ToString();
                cmd.Parameters.Add("@Name", SqlDbType.NVarChar).Value = TextBox4.Text;
                cmd.Parameters.Add("@Descn",SqlDbType.NVarChar).Value=TextBox5.Text;
                cmd.Parameters.Add("@Image", SqlDbType.NVarChar).Value = imagePath;
                cmd.Parameters.Add("@Qty",SqlDbType.Int).Value=TextBox6.Text;
                //执行操作命令：添加商品信息
                int k=cmd.ExecuteNonQuery();
                if (k > 0)
                {
                    Label4.Text = "添加商品成功！";
                    D2.Items.Clear();
                    dataselect("Select ProductId,Name from Product", D2);
                }
            }
            catch(Exception ex)
            {
                Label4.Text="添加商品错误：" + ex.Message.ToString();
            }
            finally 
            { 
                con.Close();
            }
        }

        protected void D2_SelectedIndexChanged(object sender, EventArgs e)
        {
            //选择分类，读取信息：名称，描述，显示到文本框
            SqlConnection con = new SqlConnection("Data Source=XUNDONGHUAN;Initial Catalog=mfd_sever;Integrated Security=True");
            SqlCommand cmd = new SqlCommand("Select CategoryId,ListPrice,UnitCost,SuppId,Name,Descn,Image,Qty from Product where ProductId=" + D2.SelectedValue.ToString(), con);
            con.Open();
            //执行命名的结果给reader对象
            SqlDataReader rd = cmd.ExecuteReader();
            while (rd.Read())
            {
                TextBox2.Text = rd["ListPrice"].ToString();
                TextBox3.Text = rd["UnitCost"].ToString();
                TextBox4.Text = rd["Name"].ToString();
                TextBox5.Text = rd["Descn"].ToString();
                TextBox6.Text = rd["Qty"].ToString();
            }
            con.Close();
        }

        protected void Button3_Click(object sender, EventArgs e)
        {
            //修改商品信息
            SqlConnection con = new SqlConnection("Data Source=XUNDONGHUAN;Initial Catalog=mfd_sever;Integrated Security=True");
            try
            {
                con.Open();
                //首先检查是否选择了商品
                if (string.IsNullOrEmpty(D2.SelectedValue))
                {
                    Label4.Text = "请选择要修改的商品！";
                    return;
                }
                //先获取原始图片路径
                string originalImagePath = "";
                SqlCommand getImageCmd = new SqlCommand("Select Image from Product where ProductId=@ProductId",con);
                getImageCmd.Parameters.Add("@ProductId", SqlDbType.Int).Value = D2.SelectedValue.ToString();
                object result = getImageCmd.ExecuteScalar();
                if(result !=null)
                {
                    originalImagePath = result.ToString();
                }
                //处理图片上传
                string imagesPath = originalImagePath;//使用默认原始图片路径
                if (FileUpload1.HasFile)
                {
                    string fileExtension = Path.GetExtension(FileUpload1.FileName).ToLower();
                    if (fileExtension == ".jpg" || fileExtension == ".gif")
                    {
                        string savePath = Server.MapPath("images\\");
                        string fileName= FileUpload1.FileName;
                        if (!Directory.Exists(savePath))
                        {
                            Directory.CreateDirectory(savePath);
                        }
                        FileUpload1.SaveAs(savePath + fileName);
                        imagesPath = "/images/" + fileName;
                        Image1.ImageUrl = imagesPath;
                    }
                    else
                    {
                        Label4.Text = "只支持JPG或GIF格式的图片！";
                        return;
                    }
                }
                string sql = @"update Product set CategoryId=@CategoryId,ListPrice=@ListPrice,UnitCost=@UnitCost,SuppId=@SuppId,Name=@Name,Descn=@Descn,Image=@Image,Qty=@Qty where ProductId=@ProductId";
                SqlCommand cmd = new SqlCommand(sql,con);
                //添加参数
                cmd.Parameters.Add("@CategoryId",SqlDbType.Int).Value= D3.SelectedValue.ToString();
                cmd.Parameters.Add("@ListPrice", SqlDbType.Decimal).Value = TextBox2.Text;
                cmd.Parameters.Add("@UnitCost", SqlDbType.Decimal).Value = TextBox3.Text;
                cmd.Parameters.Add("@SuppId", SqlDbType.Int).Value = D4.SelectedValue.ToString();
                cmd.Parameters.Add("@Name", SqlDbType.NVarChar).Value = TextBox4.Text;
                cmd.Parameters.Add("@Descn", SqlDbType.NVarChar).Value = TextBox5.Text;
                cmd.Parameters.Add("@Qty", SqlDbType.Int).Value = TextBox6.Text;
                cmd.Parameters.Add("@Image", SqlDbType.NVarChar).Value = imagesPath;
                cmd.Parameters.Add("@ProductId",SqlDbType.Int).Value=D2.SelectedValue.ToString();
                //执行操作命令：修改商品信息
                int k = cmd.ExecuteNonQuery();
                if (k > 0)
                {
                    Label4.Text = "修改商品成功！";
                    D2.Items.Clear();
                    dataselect("Select ProductId,Name from Product", D2);
                }
                else
                {
                    Label4.Text = "没有记录被修改！";
                }
            }
            catch(Exception ex)
            {
                Label4.Text= "修改商品错误："+ex.Message;
            }
            finally
            {
                con.Close();
            }
        }

        protected void Button4_Click(object sender, EventArgs e)
        {
            //删除商品功能
            SqlConnection con = new SqlConnection("Data Source=XUNDONGHUAN;Initial Catalog=mfd_sever;Integrated Security=True");
            try
            {
                //检查是否选择了商品
                if (string.IsNullOrEmpty(D2.SelectedValue))
                {
                    Label4.Text = "请选择要删除的商品！";
                    return;
                }
                con.Open();
                //如果有图片路径，先获取
                string originalImagePath = "";
                SqlCommand getImageCmd = new SqlCommand("Select Image from Product where ProductId=@ProductId", con);
                getImageCmd.Parameters.Add("@ProductId", SqlDbType.Int).Value = D2.SelectedValue.ToString();
                object result = getImageCmd.ExecuteScalar();
                if (result != null)
                {
                    originalImagePath = result.ToString();
                }
                //删除数据库记录
                SqlCommand deleteCmd = new SqlCommand("delete from Product where ProductId=@ProductId", con);
                deleteCmd.Parameters.Add("@ProductId",SqlDbType.Int).Value=D2.SelectedValue.ToString();
                int k = deleteCmd.ExecuteNonQuery();
                if (k > 0)
                {
                    //删除关联的图片文件
                    if (!string.IsNullOrEmpty(originalImagePath))
                    {
                        try
                        {
                            string physicalPath=Server.MapPath(originalImagePath);
                            if(File.Exists(physicalPath))
                            {
                                File.Delete(physicalPath);
                            }
                        }catch(Exception ex)
                        {
                            //删除失败，不影响主流程
                        }
                    }
                    Label4.Text = "商品删除成功！";
                    //清空表单控件
                    TextBox2.Text = "";
                    TextBox3.Text = "";
                    TextBox4.Text = "";
                    TextBox5.Text = "";
                    TextBox6.Text = "";
                    Image1.ImageUrl = "";
                    //刷新商品下拉列表
                    D2.Items.Clear();
                    dataselect("select ProductId,Name from Product", D2);
                }
                else
                {
                    Label4.Text = "删除失败，商品不存在或已经被删除！";
                }
            }
            catch(Exception ex)
            {
                Label4.Text = "数据库发生错误：" + ex.Message;
            }
        }
    }
}